/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;


import dto.FunctionDTO;
import java.beans.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.DBConfig;

/**
 *
 * @author Lada
 */
public class FunctionDAO {

    DBConfig con;
    Connection connect;
    ArrayList<FunctionDTO> list = null;
    String SQL_CREATE = "INSERT INTO Functions VALUES(?)";
    String SQL_READALL = "SELECT * FROM Accounts";
    String SQL_READBYID = "SELECT * FROM Accounts WHERE AccID = ?";
    String SQL_READBYFULLNAME = "SELECT * FROM Accounts WHERE FullName = ?";
    String SQL_UPDATE = "UPDATE Accounts SET FullName=?, ImageURL=?, RoleID=?, Address=?,Email=?,Age=?,Phone=? WHERE AccID=?";
    String SQL_DELETE = "DELETE Account WHERE AccID=?";

    public FunctionDAO() {
        con = new DBConfig();
        list = new ArrayList<FunctionDTO>();
    }

    public FunctionDTO create(FunctionDTO dto) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_CREATE);
            pst.setString(1, dto.getFunName());
           
            pst.execute();
            return dto;

        } catch (SQLException ex) {
            Logger.getLogger(FunctionDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            con.CloseConnect(connect);
        }
    }

    public ArrayList<FunctionDTO> realAll() {
        try {
            connect = con.OpenConnect();
            java.sql.Statement st = connect.createStatement();
            ResultSet rs = st.executeQuery(SQL_READALL);
            if (rs != null) {
                while (rs.next()) {
                    FunctionDTO dto = new FunctionDTO(rs.getInt(1), rs.getString(2));
                    list.add(dto);
                }
                return list;
            }
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(LoginDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            con.CloseConnect(connect);
        }

    }

    public FunctionDTO readByID(int id) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_READBYID);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            if (rs != null) {
                if (rs.next()) {
                    FunctionDTO dto = new FunctionDTO(rs.getInt(1), rs.getString(2));
                    return dto;
                }
            }
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(FunctionDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            con.CloseConnect(connect);
        }
    }

    public FunctionDTO readByName(String fName) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_READBYFULLNAME);
            pst.setString(1, fName);
            ResultSet rs = pst.executeQuery();
            if (rs != null) {
                if (rs.next()) {
                    FunctionDTO dto = new FunctionDTO(rs.getInt(1), rs.getString(2));
                    return dto;
                }
            }
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(FunctionDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            con.CloseConnect(connect);
        }
    }

    public int update(FunctionDTO dto) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst;
            pst = connect.prepareStatement(SQL_UPDATE);
            pst.setString(1, dto.getFunName());
           
            return pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(FunctionDAO.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            con.CloseConnect(connect);
        }

    }

    public int delete(int id) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst;
            pst = connect.prepareStatement(SQL_DELETE);
            pst.setInt(1, id);
            int r = pst.executeUpdate();
            return r;
        } catch (SQLException ex) {
            Logger.getLogger(FunctionDAO.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            con.CloseConnect(connect);
        }
    }
//    public static void main(String[] args) {
//        AccountDAO dao = new AccountDAO();
//        AccountDTO dto = new AccountDTO(2, "veve", "Hoahoa",1,"ds",12,"dsds","asa");
//        AccountDTO l = dao.create(dto);
////        dto.setPass("1111");
////        dto.setUser("uuu");
////        int r = dao.update(dto);
////        LoginDTO l = dao.readByID(2);
////        l = dao.readByUserName("uuu");
//        int rs = dao.delete(6);
//        //dao.delete();
//    }
}
